//引用mysql依赖
const mysql = require("mysql2");
//引用express依赖
const express = require("express");
//引用body-parser 解析post传参
const bodyParser = require("body-parser");
//express实例化
const app = express();
//引用 cors
const cors = require("cors");
const { json } = require("body-parser");
//关闭Form表单传值
app.use(bodyParser.urlencoded({ extended: false }));
//使用Json传值
app.use(bodyParser.json());
//使用cors 解决跨域问题
app.use(cors());

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root@XH2001!",
  database: "db_elder",
});
// 建立数据库连接
connection.connect();

/**
 * select
 * http://127.0.0.1:3000/api
 */
app.get("/api", (req, res) => {
  let sql = "select * from user";
  connection.query(sql, function (error, results, fields) {
    if (error) throw error;
    console.log(results);
    // return results;
    return res.json(results);
  });
});

/**
 * delete
 * http://127.0.0.1:3000/api/delete/4
 */
app.get("/api/delete/:id", (req, res) => {
  console.log(req.params);
  console.log(req.params.id);
  let delSql = `DELETE FROM user where id= ${parseInt(req.params.id)}`;
  console.log(delSql);
  connection.query(delSql, function (error, results) {
    if (error) throw error;
    console.log(results);
    // return results;
    return res.json(results);
  });
});

/**
 * insert
 * http://localhost:3000/api/insert?id=4&name=xiaohe&sex=%E7%94%B7&age=19
 */
app.get("/api/insert", (req, res) => {
  console.log(req.query);
  console.log(req.body);
  console.log(req.query.id);
  let insertSql = `insert into user VALUES(?,?,?,?)`;
  console.log(insertSql);
  let data = [req.query.id, req.query.name, req.query.sex, req.query.age];
  connection.query(insertSql, data, function (error, results) {
    if (error) throw error;
    console.log(results);
    // return results;
    return res.json(results);
  });
});

/**
 * update
 * http://localhost:3000/api/update?id=4&name=%E7%99%BE%E5%BA%A6&sex=%E7%94%B7&age=999
 */
app.get("/api/update", (req, res) => {
  console.log(req.query);
  console.log(req.body);
  console.log(req.query.id);
  let updateSql = `update user set name = ?, sex = ?, age = ? where id = ?`;
  console.log(updateSql);
  let data = [req.query.name, req.query.sex, req.query.age, req.query.id];
  connection.query(updateSql, data, function (error, results) {
    if (error) throw error;
    console.log(results);
    // return results;
    return res.json(results);
  });
});

app.listen(3000, () => {
  console.log("服务器启动成功...");
});


console.log("http://127.0.0.1:3000/api");
console.log("http://127.0.0.1:3000/api/delete/4");
console.log("http://localhost:3000/api/insert?id=4&name=xiaohe&sex=%E7%94%B7&age=19");
console.log("http://localhost:3000/api/update?id=4&name=%E7%99%BE%E5%BA%A6&sex=%E7%94%B7&age=999");

